﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace Dal
{
    public class DalPublic
    {
        SqlCommand cmd = null;
        string sql = "";

        //信息
        public DataTable Nav_Inquire_All()
        {
            //一级导航全部信息
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            DataTable dt = null;
            try
            {
                string sql = "select * from Navigation";

                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                return dt;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//一级导航全部信息

        public int Information_Count_Img_Number(ModelPublic m)
        {
            //统计推荐图片当天个数
            //InformationNumber
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            try
            {
                sql = "select count(*)as count from Information where NavId=" + m.NavId + " and RecommendRoute is not null and DateDiff(dd,DateTime,'" + m.DateTime + "')=0";

                cmd = new SqlCommand(sql, conn);
                int num = int.Parse(cmd.ExecuteScalar().ToString());
                return num;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return 0;
        }//统计推荐图片当天个数

        public int Information_Count_File_Number(ModelPublic m)
        {
            //统计资料文件当天个数
            //InformationNumber
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            try
            {
                sql = "select count(*)as count from Information where NavId='" + m.NavId + "' and FileRoute is not null and DateDiff(dd,DateTime,'" + m.DateTime + "')=0";

                cmd = new SqlCommand(sql, conn);
                int num = int.Parse(cmd.ExecuteScalar().ToString());
                return num;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return 0;
        }//统计资料文件当天个数
        
        public string Information_Id(ModelPublic m)
        {
            //信息编号
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            try
            {
                sql = "select Id from Information where Title='" + m.Title + "' and Deleted='false' and NavId='" + m.NavId + "'";

                cmd = new SqlCommand(sql, conn);
                string id = cmd.ExecuteScalar().ToString();
                if (id != "")
                    return id;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return "";
        }//信息编号
        
        public bool Information_Add(ModelPublic m)
        {
            //添加信息
            SqlConnection conn = crecteSqlConnection.sqlconn();

            conn.Open();

            try
            {
                string strContent = m.Content;
                m.Content = strContent.Replace("'", "’");

                sql = "insert into Information values(" + m.NavId + "," + m.NavSubId + "," + m.Title + ",'" + m.Content + "'," + m.DateTime + ",'"+DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss")+"'," + m.Author + "," + m.Number + "," + m.Release + "," + m.Recommend + "," + m.RecommendName + "," + m.RecommendRoute + "," + m.FileName + "," + m.FileRoute + "," + m.Remarks + ",'false')";
                cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }//添加信息

        public bool Information_IsTitle(ModelPublic m)
        {
            //判断是否存在相同标题

            SqlConnection conn = crecteSqlConnection.sqlconn();

            conn.Open();
            try
            {
                sql = "select Title from Information where Title='" + m.Title + "' and NavId='" + m.NavId + "' and Deleted='false'";
                cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteScalar().ToString() != null)
                    return true;
            }
            catch (Exception)
            {
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }//判断是否存在相同标题

        public DataTable SubNav_Inquire_All(ModelPublic m)
        {
            //二级导航全部信息
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            DataTable dt = null;
            try
            {
                sql = "select * from SubNavigation where BelongTo='" + m.NavId + "'";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                if (dt != null)
                    return dt;
            }
            catch (Exception)
            {
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//二级导航全部信息

        public DataTable Information_Inquire_All(ModelPublic m)
        {
            //获取全部信息(发布版)
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            DataTable dt = null;

            try
            {
                if (m.UserType == "0")
                    sql = "select Id,NavSubId,Title,CONVERT(varchar(10), DateTime,120) as DateTime from Information where NavId='"+m.NavId+"' and Deleted='false' and Release='True' order by DateTime desc";
                if (m.UserType == "1")
                    sql = "select Id,NavSubId,Title,CONVERT(varchar(10), DateTime,120) as DateTime from Information where NavId='"+m.NavId+"' and Deleted='false' and Release='False' and Remarks='stu' order by DateTime desc";


                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//获取全部信息(发布版)

        public DataTable Information_Inquire_All_Unpublished(ModelPublic m)
        {
            //获取全部信息(未发布版)
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            DataTable dt = null;

            try
            {
                if (m.UserType == "0")
                    sql = "select Id,NavSubId,Title,CONVERT(varchar(10), DateTime,120) as DateTime from Information where NavId='"+m.NavId+"' and Deleted='false' and Release='False' order by DateTime desc";
                if (m.UserType == "1")
                    sql = "select Id,NavSubId,Title,CONVERT(varchar(10), DateTime,120) as DateTime from Information where NavId='"+m.NavId+"' and Deleted='false' and Release='False' and Remarks='stu' order by DateTime desc";


                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//获取全部信息(未发布版)

        public DataTable Information_Inquire_Single(ModelPublic m)
        {
            //查询单条信息
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            DataTable dt = null;
            try
            {
                sql = "SELECT Id,NavId,NavSubId,Title,_Content,CONVERT(varchar(10), DateTime,120) as DateTime,Author,Number,Release,Recommend,RecommendName,RecommendRoute,FileName,FileRoute from Information where Id='" + m.Id + "'";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                if (dt != null)
                    return dt;
            }
            catch (Exception)
            {
                dt = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//查询单条信息

        public DataTable Information_Search_All(ModelPublic m)
        {
            //检索信息(发布版)
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            DataTable dt = null;

            try
            {
                if (m.UserType == "0")
                    sql = "SELECT Id,NavSubId,Title,CONVERT(varchar(10), DateTime,120) as DateTime from Information where (Title LIKE '%" + m.Conditions + "%' or DateTime like '%" + m.Conditions + "%' or Author like '%" + m.Conditions + "%') and NavId='"+m.NavId+"' and Deleted='false' and Release='true' order by DateTime desc";
                if (m.UserType == "1")
                    sql = "SELECT Id,NavSubId,Title,CONVERT(varchar(10), DateTime,120) as DateTime from Information where (Title LIKE '%" + m.Conditions + "%' or DateTime like '%" + m.Conditions + "%' or Author like '%" + m.Conditions + "%') and NavId='"+m.NavId+"' and Deleted='false' and Release='false' and Remarks='stu' order by DateTime desc";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//检索信息(发布版)

        public DataTable Information_Search_All_Unpublished(ModelPublic m)
        {
            //检索信息(未发布版)
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            DataTable dt = null;

            try
            {
                if (m.UserType == "0")
                    sql = "SELECT Id,NavSubId,Title,CONVERT(varchar(10), DateTime,120) as DateTime from Information where (Title LIKE '%" + m.Conditions + "%' or DateTime like '%" + m.Conditions + "%' or Author like '%" + m.Conditions + "%') and NavId='"+m.NavId+"' and Deleted='false' and Release='false' order by DateTime desc";
                if (m.UserType == "1")
                    sql = "SELECT Id,NavSubId,Title,CONVERT(varchar(10), DateTime,120) as DateTime from Information where (Title LIKE '%" + m.Conditions + "%' or DateTime like '%" + m.Conditions + "%' or Author like '%" + m.Conditions + "%') and NavId='"+m.NavId+"' and Deleted='false' and Release='false' and Remarks='stu' order by DateTime desc";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//检索信息(未发布版)
        
        public bool Information_Delete(ModelPublic m)
        {
            //信息删除
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            try
            {
                sql = "update Information set Deleted='true' where Id='" + m.Id + "'";
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteNonQuery() > 0)
                {
                    return true;
                }
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }//信息删除

        public bool Information_Modify(ModelPublic m)
        {
            //信息修改
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            try
            {
                string strContent = m.Content;
                m.Content = strContent.Replace("'", "’");

                sql = "update Information set NavSubId=" + m.NavSubId + ", Title=" + m.Title + ",DateTime=" + m.DateTime + ",Author=" + m.Author + ",_Content='" + m.Content + "',FileName=" + m.FileName + ",FileRoute=" + m.FileRoute + ",RecommendRoute=" + m.RecommendRoute + ",Release=" + m.Release + ",Recommend=" + m.Recommend + ",RecommendName=" + m.RecommendName + " where Id=" + m.Id + "";
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteNonQuery() > 0)
                {
                    return true;
                }
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }//信息修改


        //通知公告+周行事历

        public int Notice_Week_Count_File_Number(ModelPublic m)
        {
            //统计资料文件当天个数
            //InformationNumber
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            try
            {
                sql = "select count(*)as count from Notice where NavId='" + m.NavId + "' and FileRoute is not null and DateDiff(dd,DateTime,'" + m.DateTime + "')=0";

                cmd = new SqlCommand(sql, conn);
                int num = int.Parse(cmd.ExecuteScalar().ToString());
                return num;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return 0;
        }//统计资料文件当天个数

        public string Notice_Week_Id(ModelPublic m)
        {
            //通知公告编号
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();
            try
            {
                sql = "select Id from Notice where Titile='" + m.Title + "' and Deleted='false' and NavId='" + m.NavId + "'";

                cmd = new SqlCommand(sql, conn);
                string id = cmd.ExecuteScalar().ToString();
                if (id != "")
                    return id;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return "";
        }//通知公告编号

        public bool Notice_Week_Add(ModelPublic m)
        {
            //通知、周行事添加
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            try
            {
                string strContent = m.Content;
                m.Content = strContent.Replace("'", "’");

                sql = "insert into Notice(NavId,Title,_Content,DateTime,UpdateTime,Author,Number,Important,Weekly,Type,FileName,FileRoute,Release,Remarks)values("+m.NavId+"," + m.Title + ",'" + m.Content + "'," + m.DateTime + ",'" + DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + "'," + m.Author + "," + m.Number + ","+m.Important+"," + m.Weekly + "," + m.Type + "," + m.FileName + "," + m.FileRoute + "," + m.Release + ","+m.Remarks+")";
                cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteNonQuery() > 0)
                {
                    return true;
                }
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }//通知、周行事添加

        public bool Notice_Week_IsTitle(ModelPublic m)
        {
            //判断是否存在相同标题
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            try
            {
                sql = "select count(*)as count from Notice where Title='" + m.Title + "' and NavId='"+m.NavId+"' and Deleted='false'";

                cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteScalar().ToString() != "0")
                    return true;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }//判断是否存在相同标题

        public DataTable Notice_Week_Inquire_All(ModelPublic m)
        {
            //获取全部信息(发布版)
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            DataTable dt = null;
            try
            {
                if (m.UserType == "0")
                    sql = "SELECT Id,Title,CONVERT(char(10), DateTime, 120) AS ReleaseTime,Important FROM Notice where Type='" + m.Type + "' and NavId='"+m.NavId+"' and Deleted='false' and Release='true' order by DateTime desc";
                if (m.UserType == "1")
                    sql = "SELECT Id,Title,CONVERT(char(10), DateTime, 120) AS ReleaseTime,Important FROM Notice where Type='" + m.Type + "' and NavId='" + m.NavId + "' and Deleted='false' and Release='false' and Remarks='stu' order by DateTime desc";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                if (dt != null)
                    return dt;
            }
            catch (Exception)
            {
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//获取全部信息(发布版)

        public DataTable Notice_Week_Inquire_All_Unpublished(ModelPublic m)
        {
            //获取全部信息(未发布版)
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            DataTable dt = null;
            try
            {
                if (m.UserType == "0")
                    sql = "SELECT Id,Title,CONVERT(char(10), DateTime, 120) AS ReleaseTime,Important FROM Notice where Type='" + m.Type + "' and NavId='" + m.NavId + "' and Deleted='false' and Release='false' order by DateTime desc";
                if (m.UserType == "1")
                    sql = "SELECT Id,Title,CONVERT(char(10), DateTime, 120) AS ReleaseTime,Important FROM Notice where Type='" + m.Type + "' and NavId='" + m.NavId + "' and Deleted='false' and Release='false' and Remarks='stu' order by DateTime desc";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                if (dt != null)
                    return dt;
            }
            catch (Exception)
            {
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//获取全部信息(未发布版)

        public DataTable Notice_Week_Search_All(ModelPublic m)
        {
            //检索信息(发布版)
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            DataTable dt = null;

            try
            {
                if (m.UserType == "0")
                    sql = "SELECT Id,Title,CONVERT(char(10), DateTime, 120) AS DateTime,Important FROM Notice where (Title LIKE '%" + m.Conditions + "%' or DateTime like '%" + m.Conditions + "%' or Author like '%" + m.Conditions + "%') and Type='" + m.Type + "' and NavId='" + m.NavId + "'  and Deleted ='false' and Release='true' order by DateTime desc";
                if (m.UserType == "1")
                    sql = "SELECT Id,Title,CONVERT(char(10), DateTime, 120) AS DateTime,Important FROM Notice where (Title LIKE '%" + m.Conditions + "%' or DateTime like '%" + m.Conditions + "%' or Author like '%" + m.Conditions + "%') and Type='" + m.Type + "' and NavId='" + m.NavId + "'  and Deleted ='false' and Release='false' and Remarks='stu' order by DateTime desc";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//检索信息(发布版)

        public DataTable Notice_Week_Search_All_Unpublished(ModelPublic m)
        {
            //检索信息(未发布版)
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            DataTable dt = null;

            try
            {
                if (m.UserType == "0")
                    sql = "SELECT Id,Title,CONVERT(char(10), DateTime, 120) AS ReleaseTime,Important FROM Notice where (Title LIKE '%" + m.Conditions + "%' or DateTime like '%" + m.Conditions + "%' or Author like '%" + m.Conditions + "%') and Type='" + m.Type + "' and NavId='" + m.NavId + "'  and Deleted ='false' and Release='false' order by DateTime desc";
                if (m.UserType == "1")
                    sql = "SELECT Id,Title,CONVERT(char(10), DateTime, 120) AS ReleaseTime,Important FROM Notice where (Title LIKE '%" + m.Conditions + "%' or DateTime like '%" + m.Conditions + "%' or Author like '%" + m.Conditions + "%') and Type='" + m.Type + "' and NavId='" + m.NavId + "'  and Deleted ='false' and Release='false' and Remarks='stu' order by DateTime desc";
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                dt = ds.Tables[0];
                return dt;
            }
            catch (Exception)
            {
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//检索信息(未发布版)

        public DataTable Notice_Week_Inquire_Single(ModelPublic m)
        {
            //查询单条信息
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            DataTable dt = null;

            try
            {
                sql = "select Id,Title,_Content,CONVERT(char(10), DateTime, 120) AS DateTime,Author,Number,Important,Weekly,Type,FileName,FileRoute,Release from Notice where Id='" + m.Id + "'";
                cmd = new SqlCommand(sql, conn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds, "table");
                dt = ds.Tables["table"];
                if (dt != null)
                    return dt;
            }
            catch (Exception)
            {
                dt = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return dt;
        }//查询单条信息

        public bool Notice_Week_Delete(ModelPublic m)
        {
            //信息删除
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();

            try
            {
                sql = "update Notice set Deleted='true' where Id='" + m.Id + "'";
                cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }//信息删除

        public bool Notice_Week_Modify(ModelPublic m)
        {
            //信息修改
            SqlConnection conn = crecteSqlConnection.sqlconn();
            conn.Open();


            try
            {
                string strContent = m.Content;
                m.Content = strContent.Replace("'", "’");

                sql = "update Notice set Title=" + m.Title + ",Weekly=" + m.Weekly + ",DateTime=" + m.DateTime + ",_Content='" + m.Content + "',Important=" + m.Important + ",Number=" + m.Number + ",FileName=" + m.FileName + ",FileRoute=" + m.FileRoute + ",Release=" + m.Release + " where Id=" + m.Id + "";
                cmd = new SqlCommand(sql, conn);
                if (cmd.ExecuteNonQuery() > 0)
                    return true;
            }
            catch (Exception)
            {

            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return false;
        }//信息修改
    }
}
